/**
 * 
 * Copyright 2009 Marco Speranza <marco.speranza79@gmail.com>
 *    
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *   
 * http://www.apache.org/licenses/LICENSE-2.0
 *  
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package org.commons.database.util.jquerycreator;

import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.HashMap;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.commons.util.conversion.DateConv;

// TODO: Auto-generated Javadoc
/**
 * Classe per la creazione delle query sql. Questa classe non e' sincronizzata.
 * In ambiente multithreading utilizzare
 * <code> SyncronizedDataBaseQueryCreator </code>
 * 
 * Ogni metodo che lancia una SQLException chude tutte le risorse eventualmente
 * aperte al datasource in caso di SQLException.
 * 
 * un tipico uso e':
 * 
 * <pre>
 * dbqc = new DataBaseQueryCreator();
 * dbqc.setDataSourceManager(datasourceManager); // e' una implementazione dell'interfaccia IDatasourceManager
 * dbqc.setSql(&quot;SELECT id, testblob FROM test where id =?&quot;);
 * dbqc.put(&quot;pippo&quot;, Types.VARCHAR);
 * try {
 *     dbqc.fillQuery();
 *     dbqc.executeQuery();
 *     ResultSet tupla = dbqc.getResultSet();
 *     while (tupla.next()) {
 * 	int id = tupla.getInt(&quot;id&quot;);
 * 	Blob b = tupla.getBlob(&quot;testblob&quot;);
 *     }
 * } catch (SQLException e) {
 *     e.printStackTrace();
 *     System.err.println(e.getMessage());
 * } finally {
 *     dbqc.closeAll();
 * }
 * </pre>
 * 
 * oppure:
 * 
 * <pre>
 * dbqc = new DataBaseQueryCreator();
 * dbqc.setDataSourceManager(datasourceManager); // e' una implementazione dell'interfaccia IDatasourceManager
 * dbqc.setSql(&quot;SELECT id, testblob FROM test where id =?&quot; FOR UPDATE);
 * dbqc.put(&quot;pippo&quot;, Types.VARCHAR);
 * try {
 *     dbqc.fillQuery();
 *     dbqc.executeQuery();
 *     ResultSet tupla = dbqc.getResultSet();
 *     while (tupla.next()) {
 * 	int id = tupla.getInt(&quot;id&quot;);
 * 	Blob b = tupla.getBlob(&quot;testblob&quot;);
 *     }
 * } catch (SQLException e) {
 *     e.printStackTrace();
 *     System.err.println(e.getMessage());
 *     dbqc.closeAll();
 * } finally {
 *     dbqc.closeAllWithoutConnection();  //vengono chiuse tutte le risorse ECCETTO la connessione.
 * }
 * 
 * dbqc.clear();
 * dbqc.setSql(&quot;UPDATE test set id = ? where id =?&quot;);
 * dbqc.put(&quot;pluto&quot;, Types.VARCHAR);
 * dbqc.put(&quot;pippo&quot;, Types.VARCHAR);
 * try {
 *     dbqc.fillQuery();
 *     int num = dbqc.executeUpdate();
 *     
 *     dbqc.commit();   // NON DIMENTICARSI DI FARE LA COMMIT!!
 *     
 * } catch (SQLException e) {
 *     e.printStackTrace();
 *     System.err.println(e.getMessage());
 *     dbqc.closeAll();
 * } finally {
 *     dbqc.closeAll();  
 * }
 * </pre>
 * 
 * @author Marco Speranza
 */
public class DataBaseQueryCreator implements IDataBaseQueryCreator {

    /** The Constant logger. */
    private static final Log logger = LogFactory
	    .getLog(DataBaseQueryCreator.class);

    /** The Constant serialVersionUID. */
    private static final long serialVersionUID = 8999935174846078473L;

    /** The autogenerated key. */
    private int autogeneratedKey = Statement.NO_GENERATED_KEYS;

    /** The connection. */
    private Connection connection;

    /** The datasource manager. */
    private IDatasourceManager datasourceManager;

    /** The maps. */
    private Map<String, DataBaseQueryCreatorBean> maps;

    /** The position. */
    private int position = 0;

    /** The result set. */
    private ResultSet resultSet;

    /** The sql. */
    private String sql;

    /** The statement. */
    private PreparedStatement statement;

    /**
     * Instantiates a new data base query creator.
     */
    public DataBaseQueryCreator() {
	this(Statement.NO_GENERATED_KEYS);
    }

    /**
     * Instantiates a new data base query creator.
     * 
     * @param autogeneratedKey
     *            the autogenerated key
     */
    public DataBaseQueryCreator(int autogeneratedKey) {
	maps = new HashMap<String, DataBaseQueryCreatorBean>(1);
	setAutogeneratedKey(autogeneratedKey);
    }

    /*
     * (non-Javadoc)
     * 
     * @see
     * org.commons.database.util.jquerycreator.IDataBaseQueryCreator#commit()
     */
    public void commit() throws SQLException {
	if (connection != null && connection.isClosed() == false) {
	    connection.commit();
	    logger.debug("commit executed");
	}
    }

    /**
     * Esegue la rollback sulla connessione.
     * 
     * in caso di SQLException non viene propagata l'eccezione.
     * 
     * @throws SQLException
     */
    protected void rollback() throws SQLException {
	if (connection != null && connection.isClosed() == false) {
	    connection.rollback();
	    logger.debug("rollback executed");
	}
    }

    /**
     * Rimuove tutti gli elemti dalla mappa.
     */
    public void clear() {
	maps.clear();
	position = 0;
	sql = null;
    }

    /*
     * (non-Javadoc)
     * 
     * @see
     * org.commons.database.util.jquerycreator.IDataBaseQueryCreator#closeAll()
     */
    public void closeAll() throws SQLException {
	logger.debug("Closing All..");

	JdbcUtils.close(resultSet, statement);
	datasourceManager.releaseConnection(connection);
	resultSet = null;
	statement = null;
	connection = null;
	logger.debug("...done!.");

    }

    /*
     * (non-Javadoc)
     * 
     * @seeorg.commons.database.util.jquerycreator.IDataBaseQueryCreator#
     * closeAllWithoutConnection()
     */
    public void closeAllWithoutConnection() throws SQLException {
	JdbcUtils.close(resultSet, statement);
	resultSet = null;
	statement = null;
    }

    /*
     * (non-Javadoc)
     * 
     * @see
     * org.commons.database.util.jquerycreator.IDataBaseQueryCreator#executeQuery
     * ()
     */
    public void executeQuery() throws SQLException {
	try {
	    resultSet = statement.executeQuery();
	} catch (SQLException e) {
	    logger.error(e.getMessage(), e);
	    rollback();
	    closeAll();

	    throw e;
	}
    }

    /*
     * (non-Javadoc)
     * 
     * @see
     * org.commons.database.util.jquerycreator.IDataBaseQueryCreator#executeUpdate
     * ()
     */
    public int executeUpdate() throws SQLException {
	try {
	    int rows = statement.executeUpdate();
	    return rows;
	} catch (SQLException e) {
	    logger.error(e.getMessage(), e);
	    rollback();
	    closeAll();
	    throw e;
	}

    }

    /*
     * (non-Javadoc)
     * 
     * @see
     * org.commons.database.util.jquerycreator.IDataBaseQueryCreator#executeUpdate
     * (int[])
     */
    public int executeUpdate(int[] columnIndex) throws SQLException {
	try {
	    int num = statement.executeUpdate(getSql(), columnIndex);
	    resultSet = statement.getGeneratedKeys();
	    return num;
	} catch (SQLException e) {
	    logger.error(e.getMessage(), e);
	    rollback();
	    closeAll();
	    throw e;
	}
    }

    /*
     * (non-Javadoc)
     * 
     * @see
     * org.commons.database.util.jquerycreator.IDataBaseQueryCreator#fillQuery()
     */
    public void fillQuery() throws SQLException {
	try {
	    if (statement == null) {
		makePreparedStatement();
	    }
	    fillQuery(statement);
	} catch (SQLException e) {
	    logger.error(e.getMessage(), e);
	    rollback();
	    closeAll();
	    throw e;
	}

    }

    /**
     * Sostituisce i '?' con i valori associati.
     * 
     * @param stmt
     *            N.B. <code>stmt</code> deve essere la stessa instanza della
     *            variabile interna <code>statement</code> Utilizzare il meetodo
     *            <code>makePreparedStatement</code> per creare una instanza
     *            dell'oggetto stmt.
     * 
     * @return the prepared statement
     * 
     * @throws SQLException
     *             in caso di errore chude le risorse al database, compreso il
     *             preparestatement in input
     */
    public PreparedStatement fillQuery(final PreparedStatement stmt)
	    throws SQLException {
	try {
	    if (stmt != statement) {
		throw new IllegalArgumentException(
			"the argument must be the same object of statement field."
				+ " Use makePreparedStatement() or "
				+ " makePreparedStatement(String)  to create this object.");
	    }

	    if (!isAllFilled())
		throw new IllegalArgumentException("All '?' must be filled.");

	    for (int i = 0; i < maps.size(); i++) {
		DataBaseQueryCreatorBean bean = maps.get("" + (i + 1));
		fillValue(stmt, bean);
	    }
	    return stmt;
	} catch (SQLException e) {
	    rollback();
	    closeAll();
	    throw e;
	}
    }

    /*
     * (non-Javadoc)
     * 
     * @see
     * org.commons.database.util.jquerycreator.IDataBaseQueryCreator#fillQuery
     * (java.lang.String)
     */
    public void fillQuery(final String sql) throws SQLException {
	try {
	    if (statement == null) {
		makePreparedStatement(sql);
	    } else {
		setSql(sql);
	    }

	    fillQuery(statement);
	} catch (SQLException e) {
	    logger.error(e.getMessage(), e);
	    rollback();
	    closeAll();
	    throw e;
	}

    }

    /**
     * Tornala query sql in formato testo sostituendo i '?' con i valori
     * inseriti.
     * 
     * @return the filled query
     */
    public String getFilledQuery() {
	String sql = getSql();
	if (sql == null)
	    return "";

	Pattern myPattern = Pattern.compile("\\?");

	Matcher myMatcher = myPattern.matcher(sql);

	int index = 0;
	StringBuffer buf = new StringBuffer();
	while ((myMatcher.find())) {

	    DataBaseQueryCreatorBean bean = maps.get("" + (index + 1));

	    if (bean != null) {

		switch (bean.getType()) {
		case Types.VARCHAR:
		    myMatcher.appendReplacement(buf, "'" + bean.getValue()
			    + "'");
		    break;
		case Types.INTEGER:
		    myMatcher.appendReplacement(buf, "" + bean.getValue());
		    break;
		case Types.TIMESTAMP:
		    Timestamp t = (Timestamp) bean.getValue();
		    String repStr = "TO_DATE('"
			    + DateConv.toString(t.getTime())
			    + "','yyyy-mm-dd hh24:mi:ss')";
		    myMatcher.appendReplacement(buf, repStr);
		    break;
		case Types.NULL:
		    myMatcher.appendReplacement(buf, "null");
		    break;
		case Types.CLOB:
		    myMatcher.appendReplacement(buf, "'" + bean.getValue()
			    + "'");
		    break;
		case Types.BLOB:
		    myMatcher.appendReplacement(buf, "'" + bean.getValue()
			    + "'");
		    break;
		default:
		    myMatcher.appendReplacement(buf, "'" + bean.getValue()
			    + "'");
		}

	    } else {
		myMatcher.appendReplacement(buf, "?");
	    }
	    index++;
	}
	myMatcher.appendTail(buf);
	String result = buf.toString();
	return result;
    }

    /**
     * Gets the position.
     * 
     * @return the position
     */
    public int getPosition() {
	return position;
    }

    /*
     * (non-Javadoc)
     * 
     * @see
     * org.commons.database.util.jquerycreator.IDataBaseQueryCreator#getResultSet
     * ()
     */
    public ResultSet getResultSet() {
	return resultSet;
    }

    /*
     * (non-Javadoc)
     * 
     * @see
     * org.commons.database.util.jquerycreator.IDataBaseQueryCreator#getSql()
     */
    public String getSql() {
	return sql;
    }

    /*
     * (non-Javadoc)
     * 
     * @seeorg.commons.database.util.jquerycreator.IDataBaseQueryCreator#
     * makePreparedStatement()
     */
    public PreparedStatement makePreparedStatement() throws SQLException {

	try {
	    if (getSql() == null || getSql().trim().equals("")) {
		throw new IllegalArgumentException("Sql string can't be null. ");
	    }
	    makeConnection();
	    statement = connection.prepareStatement(getSql(), autogeneratedKey);
	    return statement;
	} catch (SQLException e) {
	    logger.error(e.getMessage(), e);
	    rollback();
	    closeAll();
	    throw e;
	}
    }

    /*
     * (non-Javadoc)
     * 
     * @seeorg.commons.database.util.jquerycreator.IDataBaseQueryCreator#
     * makePreparedStatement(java.lang.String)
     */
    public PreparedStatement makePreparedStatement(final String sql)
	    throws SQLException {
	try {
	    setSql(sql);
	    return makePreparedStatement();
	} catch (SQLException e) {
	    logger.error(e.getMessage(), e);
	    rollback();
	    closeAll();
	    throw e;
	}
    }

    /**
     * Put.
     * 
     * @param value
     *            the value
     * @param type
     *            the type
     * 
     * @return the object
     * 
     * @see IDataBaseQueryCreator.put(Object , int);
     */
    public DataBaseQueryCreatorBean put(final Object value, final int type) {
	DataBaseQueryCreatorBean bean = new DataBaseQueryCreatorBean(value,
		type);
	bean.setPosition(++position);
	return maps.put("" + position, bean);
    }

    /*
     * (non-Javadoc)
     * 
     * @seeorg.commons.database.util.jquerycreator.IDataBaseQueryCreator#
     * setAutogeneratedKey(int)
     */
    public void setAutogeneratedKey(final int autogeneratedKey) {
	this.autogeneratedKey = autogeneratedKey;
    }

    /*
     * (non-Javadoc)
     * 
     * @seeorg.commons.database.util.jquerycreator.IDataBaseQueryCreator#
     * setDataSourceManager
     * (org.commons.database.util.jquerycreator.IDatasourceManager)
     */
    public void setDataSourceManager(final IDatasourceManager datasourceManager) {
	this.datasourceManager = datasourceManager;
    }

    /*
     * (non-Javadoc)
     * 
     * @see
     * org.commons.database.util.jquerycreator.IDataBaseQueryCreator#setSql(
     * java.lang.String)
     */
    public void setSql(final String sql) {
	this.sql = sql;
    }

    /*
     * (non-Javadoc)
     * 
     * @see java.lang.Object#toString()
     */
    public String toString() {
	StringBuffer buff = new StringBuffer(1);
	for (int i = 0; i < maps.size(); i++) {
	    DataBaseQueryCreatorBean bean = ((DataBaseQueryCreatorBean) maps
		    .get("" + (i + 1)));
	    buff.append(" Position: ");
	    buff.append(bean.getPosition());
	    buff.append(" - Value: ");
	    buff.append(bean.getValue());
	    buff.append("\n");
	}
	return buff.toString();
    }

    /*
     * (non-Javadoc)
     * 
     * @see java.lang.Object#finalize()
     */
    protected void finalize() throws Throwable {
	super.finalize();
	closeAll();
	clear();
    }

    /**
     * Fill value.
     * 
     * @param stmt
     *            the stmt
     * @param bean
     *            the bean
     * 
     * @throws SQLException
     *             the SQL exception
     */
    private void fillValue(final PreparedStatement stmt,
	    final DataBaseQueryCreatorBean bean) throws SQLException {
	if (bean.getType() == Types.CLOB) {
	    if (bean.getValue() instanceof String) {
		InputStream is = new ByteArrayInputStream(((String) bean
			.getValue()).getBytes());
		int length = ((String) bean.getValue()).length();
		stmt.setAsciiStream(bean.getPosition(), is, length);
	    } else if (bean.getValue() instanceof Clob) {
		stmt.setClob(bean.getPosition(), (Clob) bean.getValue());
	    } else {
		throw new IllegalArgumentException(
			"A Clob type must be a Clob or String instance.");
	    }
	} else if (bean.getType() == Types.BLOB) {
	    if (bean.getValue() instanceof byte[]) {
		InputStream is = new ByteArrayInputStream((byte[]) bean
			.getValue());
		int length = ((byte[]) bean.getValue()).length;
		stmt.setBinaryStream(bean.getPosition(), is, length);
	    } else if (bean.getValue() instanceof Blob) {
		stmt.setBlob(bean.getPosition(), (Blob) bean.getValue());
	    } else {
		throw new IllegalArgumentException(
			"A Blob type must be a Blob or byte[] instance.");
	    }
	} else {
	    stmt.setObject(bean.getPosition(), bean.getValue(), bean.getType());
	}
    }

    /**
     * Checks if is all filled.
     * 
     * @return true, if is all filled
     */
    private boolean isAllFilled() {
	String sql = getSql();
	if (sql == null)
	    return false;

	Pattern myPattern = Pattern.compile("\\?");

	Matcher myMatcher = myPattern.matcher(sql);

	int index = 0;
	while ((myMatcher.find())) {
	    index++;
	}

	return (maps.size() == index);
    }

    /**
     * Make connection.
     * 
     * @throws SQLException
     *             the SQL exception
     */
    private void makeConnection() throws SQLException {
	if (datasourceManager == null)
	    throw new NullPointerException("databasesourceManager must be set.");
	if (this.connection == null || this.connection.isClosed()) {
	    connection = datasourceManager.getConnection();
	}
    }

}
